﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class DSTVRMUData : BaseFunction
    {

        byte[] b;
        string value;
        public DSTVRMUData()
            : base()
        {

        }
        public DSTVRMUData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataRow dwUI)
        {
            string TableName = "VRMU";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string ORDER,SO,MAN,CUSTOMER,EXP,USER;
            ORDER = dwUI["ORDER"].ToString().Trim();
            SO = dwUI["SO"].ToString().Trim();
            MAN = dwUI["MAN"].ToString().Trim();
            CUSTOMER = dwUI["CUSTOMER"].ToString().Trim();
            EXP = dwUI["EXP"].ToString().Trim();
            USER = dwUI["USER"].ToString().Trim();

            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.VRKRENR,B.VRPREPOS,A.VRKREART,A.VRKREDAT,A.VRKKUNDNR,K.KDKUNDNA, "
                    + " A.VRKSACHBE,TRIM(D.HRVNAME)||TRIM(D.HRNNAME) AS NAME,B.VRPVG,E.VGDES,B.VRPBUSIMODE, "
                    + " TRIM(B.VRPAUFKRS)||TO_NUMBER(B.VRPAUFLFD)||'-'||TO_NUMBER(B.VRPAUFPOS) AS CO,B.VRPART,B.VRPFARBE, "
                    + " B.VRPMENGE*0.01 AS INVQTY,B.VRPMEEH,B.VRPPREIS*0.001 AS INVPR,B.VRPPREH,B.VRPWAEHRSL, "
                    + " C.VKPMENGE1*0.01 AS COQTY,C.VKPVERS*0.01 AS DEQTY,C.VKPMEEH,C.VKPPREIS*0.001 AS COPR "
                    + " ,C.VKPPREH,C.VKPHWAEHRSL,TRIM(C.VKPKDARTNR) AS CSART, "
                    + " TRIM(B.VRPZUART1)||' '||TO_CHAR(ROUND(B.VRPZUABWERT1*0.01,2),'fm9999999990.00') AS SURCHARGE1, "
                    + " TRIM(B.VRPZUART2)||' '||TO_CHAR(ROUND(B.VRPZUABWERT2*0.01,2),'fm9999999990.00') AS SURCHARGE2, "
                    + " TRIM(B.VRPZUART3)||' '||TO_CHAR(ROUND(B.VRPZUABWERT3*0.01,2),'fm9999999990.00') AS SURCHARGE3, "
                    + " ROUND((B.VRPZUABWERT1+B.VRPZUABWERT2+B.VRPZUABWERT3)*0.01+ "
                    + " DECODE(TRIM(B.VRPMEEH),'Y',B.VRPMENGE*0.9144,B.VRPMENGE)*0.01 "
                    + " *DECODE(TRIM(B.VRPPREH),'Y',B.VRPPREIS/0.9144,B.VRPPREIS)*0.001,2) AS VALUE, "
                    + " '' AS ARTBEM,'' AS INVBEM,'' AS REMARK,'' AS SHIPDAT,A.VRKKOMPL AS PAY "
                    + " FROM VRKOPF A,KDSTAMM K,VRPOS B,VKPOS C,HRSTAMM D, "
                    + " (SELECT SUBSTR(SDKEY,3,3) AS VG,SUBSTR(SDDATEN,2,20)AS VGDES FROM SDDAT  "
                    + " WHERE SDSA = '0183' AND SUBSTR(SDKEY,1,2) ='00' "
                    + " AND SUBSTR(SDKEY,24,1)='V') E WHERE A.VRKMANDANT = K.KDMANDANT "
                    + " AND A.VRKKUNDNR = K.KDKUNDNR AND A.VRKMANDANT = B.VRPMANDANT "
                    + " AND A.VRKRENR = B.VRPRENR AND B.VRPMANDANT = C.VKPMANDANT "
                    + " AND B.VRPAUFKRS = C.VKPAUFKRS AND B.VRPAUFLFD = C.VKPAUFLFD "
                    + " AND B.VRPAUFPOS = C.VKPAUFPOS AND A.VRKMANDANT = D.HRMANDANT "
                    + " AND A.VRKSACHBE = D.HRBENID AND B.VRPVG = E.VG AND C.VKPSA <> 'AJ'"
                    + " AND A.VRKFIBUKZ<>'S' AND B.VRPSTORNO <>'J' "
                    + " AND (A.VRKRENR = '" + ORDER + "' OR '" + ORDER + "' = '00000000' )  "
                    + " AND (TRIM(B.VRPAUFKRS)||TO_NUMBER(B.VRPAUFLFD)||'-'||TO_NUMBER(B.VRPAUFPOS) = '" + SO + "' "
                    + "  OR '" + SO + "' ='AAA' ) "
                    + " AND (trim(A.VRKSACHBE) = '" + MAN + "' OR '" + MAN + "' = 'AAA' )  "
                    + " AND (A.VRKKUNDNR = '" + CUSTOMER + "' OR '" + CUSTOMER + "' = '00000000' )  "
                    + " AND (trim(A.VRKVB) = '" + EXP + "' OR '" + EXP + "' = 'AAA' )  "
                    + " AND (trim(B.VRPVG) = '" + USER + "' OR '" + USER + "' = 'AAA' )  "
                    + " ORDER BY 2 "
                    ;
                //orlReadCommand.Parameters.Add("ORDER", OracleType.NVarChar).Value = dwUI["ORDER"].ToString().Trim();
                //WBBBESCHICH
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    string INVPOS = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "NAME";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "VGDES";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        Column = "CSART";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[Column] = value;
                        #endregion
                        INVPOS = dwTemp["VRKRENR"].ToString().Trim() + dwTemp["VRPREPOS"].ToString().Trim();
                        dwTemp["INVBEM"] = GetCommentsFromTXTIND("RES", ORDER);
                        dwTemp["REMARK"] = GetCommentsFromTXTIND("REP",INVPOS);
                        GetWASTAMM(dwTemp);
                        GetSHIPDAT(dwTemp);
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetWASTAMM(DataRow dwUI)
        {
            string TableName = "WASTAMM";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string ARTICLE;
            ARTICLE = dwUI["VRPART"].ToString();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select TRIM(WAFBEM) AS WAFBEM from wastamm a  "
                    + " where a.waart = SUBSTR('"+ARTICLE+"',1,20) "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "WAFBEM";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwUI["ARTBEM"] = value;
                        #endregion
                       

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetSHIPDAT(DataRow dwUI)
        {
            string TableName = "SHIPDAT";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string CO;
            CO = dwUI["CO"].ToString().Trim();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT MAX(WBEWBUCHDAT) AS SHIPDAT FROM WBBEW A "
                    + " WHERE A.WBEWAUFTYP = 'K' AND A.WBEWBS = '31' and a.wbewmandant = '00' "
                    + " AND TRIM(A.WBEWAUFKRS)||TO_NUMBER(A.WBEWAUFLFD)||'-'||TO_NUMBER(A.WBEWAUFPOS) = '" + CO + "' "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;

                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        dwUI["SHIPDAT"] = dwTemp["SHIPDAT"].ToString().Trim();

                    }

                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            string progress = "查询完成";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                EventDispatcher.Instance.RaiseStatus("查询开始", StatusEventType.StartProgress);
                setResult = GetWBBST(dwUI);
                setReturn.Merge(setResult);
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}